import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

my_student_router = APIRouter(prefix="/school/tsStudent", tags=["学生查询管理"], dependencies=[Depends(oauth2_scheme)])


class StudentSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fclassno: Optional[str] = Field("", description="班级编号")
    fstudentname: Optional[str] = Field("", description="学生姓名(支持前缀匹配)")


@my_student_router.get("/list", summary="学生查询接口")
def list_my_student(search: StudentSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_ts_student where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fclassno != "":
        sql = sql + " and fclass_no = :fclassno "
        param["fclassno"] = search.fclassno
    if search.fstudentname != "":
        sql = sql + " and fstudent_name like concat('%', :fstudentname ,'%')"
        param["fstudentname"] = search.fstudentname
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fstudent_no,fstudent_name,fsite_no,fclass_no,fwork_no,"
    sql = sql + " fuserno,fnote,fstate,fupddate,foperator"
    sql = sql + " from a_ts_student where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fclassno != "":
        sql = sql + " and fclass_no = :fclassno "
        param["fclassno"] = search.fclassno
    if search.fstudentname != "":
        sql = sql + " and fstudent_name like concat('%', :fstudentname ,'%')"
        param["fstudentname"] = search.fstudentname
    if search.order == "desc":
        sql = sql + " order by  fstudent_no desc "
    else:
        sql = sql + " order by  fstudent_no "
    sql = sql + " limit :limit offset :offset "
    param["offset"] = search.offset
    param["limit"] = search.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fstudentno'] = data[key][0]
        d1['fstudentname'] = data[key][1]
        d1['fsiteno'] = data[key][2]
        d1['fclassno'] = data[key][3]
        d1['fworkno'] = data[key][4]
        d1['fuserno'] = data[key][5]
        d1['fnote'] = data[key][6]
        d1['fstate'] = data[key][7]
        d1['fupddate'] = data[key][8]
        d1['foperator'] = data[key][9]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class StudentCreate(BaseModel):
    """学生资料新增请求参数"""
    fstudentno: str = Field(..., description="学生编号")
    fstudentname: str = Field(..., description="学生姓名")
    fsiteno: str = Field(..., description="站点编号")
    fclassno: str = Field(..., description="班级编号")
    fstate: int = Field(..., description="学生状态")
    fupddate: str = Field(..., description="更新日期(格式需与数据库存储一致)")
    fworkno: Optional[str] = Field("", description="工作编号，默认为空")
    fuserno: Optional[str] = Field("0", description="用户编号，默认为0")
    fnote: Optional[str] = Field("", description="备注信息，默认为空")


@my_student_router.post("/create", summary="学生资料新增接口")
def create_my_student(create: StudentCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "select 1 from a_ts_student where fstudent_no= :fstudentno "
            data = db.execute(text(sql), {"fstudentno": create.fstudentno}).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="学生编号已存在")
            else:
                sql = "insert into a_ts_student(fstudent_no,fstudent_name,fsite_no,fclass_no,fwork_no,fuserno,fnote,fstate,fupddate,foperator)"
                sql = sql + " values( :fstudentno , :fstudentname , :fsiteno , :fclassno , :fworkno , :fuserno , :fnote , :fstate ,now(), :userid );"
                db.execute(text(sql), {
                    "fstudentno": create.fstudentno,
                    "fstudentname": create.fstudentname,
                    "fsiteno": create.fsiteno,
                    "fclassno": create.fclassno,
                    "fworkno": create.fworkno,
                    "fuserno": create.fuserno,
                    "fnote": create.fnote,
                    "fstate": create.fstate,
                    "userid": user_id
                })

                data = {"fstudentno": create.fstudentno
                    , "fstudentname": create.fstudentname
                    , "fsiteno": create.fsiteno
                    , "fclassno": create.fclassno
                    , "fworkno": create.fworkno
                    , "fnote": create.fnote
                    , "fstate": create.fstate
                    , "fupddate": create.fupddate
                    , "foperator": user_id
                        }
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@my_student_router.post("/update", summary="学生修改接口")
def update_my_student(update: StudentCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update a_ts_student"
            sql = sql + " set fstudent_name= :fstudentname ,fsite_no= :fsiteno "
            sql = sql + ",fclass_no= :fclassno ,fwork_no= :fworkno ,fuserno= :fuserno "
            sql = sql + ",fnote= :fnote ,fstate= :fstate ,fupddate=now(),foperator= :userid "
            sql = sql + " where fstudent_no= :fstudentno "
            db.execute(text(sql), {
                "fstudentname": update.fstudentname,
                "fsiteno": update.fsiteno,
                "fclassno": update.fclassno,
                "fworkno": update.fworkno,
                "fuserno": update.fuserno,
                "fnote": update.fnote,
                "fstate": update.fstate,
                "userid": user_id,
                "fstudentno": update.fstudentno
            })
            return BaseResponse.success()

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@my_student_router.post("/delete/{fstudentno}", summary="学生删除接口")
def delete_my_student(fstudentno: str = Path(description=""), db: Session = Depends(get_db),
                      token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "delete from a_ts_student where fstudent_no= :fstudentno "
            db.execute(text(sql), {
                "fstudentno": fstudentno
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
